SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TCMOVI_BANC_Q15]                            
/*-------------------------------------------------------*/                            
/*---Empresa               OFISIS S.A.                --*/                            
/*---Cliente               CELIMA                     --*/                            
/*---Sistema               Tesoreria                  --*/                            
/*---M¢dulo                Empresa                    --*/                            
/*---Programa              FLUJO DE CAJA REAL SEMANAL (REPORTE)      --*/                            
/*---Script                SP_TCMOVI_BANC_Q03.sql     --*/                            
/*---Nombre SP             SP_TCMOVI_BANC_Q03         --*/                            
/*---Desarrollado por      Reynaldo Menacho Luyo      --*/                            
/*---Fecha Creaci¢n        25/09/2001                 --*/                            
/*---Base Datos            SQL SERVER        --*/                            
/*---Versi¢n               2000                       --*/                            
/*---Invoca a SP                                      --*/                            
/*-------------------------------------------------------*/                            
                            
/*-------------------------------------------------------*/                            
/*---Modificado 1 por           --*/                            
/*---Fecha Modificaci¢n         --*/                             
/*---Detalle Modificaci¢n       --*/                            
/*-------------------------------------------------------*/                            
                            
/*-------------------------------------------------------*/                            
/*---Drop Proc SP_TCMOVI_BANC_Q03                      --*/                            
/*---Grant all on SP_TCMOVI_BANC_Q03 to public         --*/                             
/*---SP_TCMOVI_BANC_Q03 '01', 'DOL', 10, 2001, 11, 2001  --*/                            
/*-------------------------------------------------------*/                            
                            
--		EXEC SP_TCMOVI_BANC_Q15 '01', 'WHERE 1 = 1', '', 'U', 'SOL','2011-01-31'		'2011/01/31'               
                          
@ISCO_EMPR Varchar(2),                              
@ISCA_WHER_0001 Varchar(256),                                
@ISCA_WHER_0002 Varchar(256),                           
@ISTI_PRES TD_VC_001,                               
@ISCO_MONE TD_VC_003,                                
@IDFE_OPER_QUIE TD_DT_001                                
    
/*,                            
@INNU_MESE_FINA TD_IN_001,                            
@INNU_ANIO_FINA TD_IN_001  */                          
As                            
CREATE TABLE #TWFLUJ_CAJA_Q15_1                            
 ( CO_EMPR      varchar(3) NULL,         
   DE_EMPR      varchar(100) NULL,        
   IM_SALD_FLUJ numeric(16,4) DEFAULT 0,                               
   CO_ARUB varchar(3) NULL ,          
   DE_ARUB varchar(100) NULL ,                         
   CO_RUBR varchar(3) NULL ,          
   DE_RUBR varchar(100) NULL ,                          
   CO_SUBR varchar(3) NULL ,           
   DE_SUBR varchar(100) NULL ,                        
   IM_SE01 numeric(16,4) DEFAULT 0,                            
   IM_SE02 numeric(16,4) DEFAULT 0,                            
   IM_SE03 numeric(16,4) DEFAULT 0,                            
   IM_SE04 numeric(16,4) DEFAULT 0,                            
   IM_SE05 numeric(16,4) DEFAULT 0,                            
   IM_SE06 numeric(16,4) DEFAULT 0,                            
   IM_SE07 numeric(16,4) DEFAULT 0,                            
   IM_SE08 numeric(16,4) DEFAULT 0,                            
   IM_SE09 numeric(16,4) DEFAULT 0,                            
   IM_SE10 numeric(16,4) DEFAULT 0,                            
   IM_SE11 numeric(16,4) DEFAULT 0,                            
   IM_SE12 numeric(16,4) DEFAULT 0,                       
   IM_SE13 numeric(16,4) DEFAULT 0,                            
   IM_SE14 numeric(16,4) DEFAULT 0,                            
   IM_SE15 numeric(16,4) DEFAULT 0,                            
   IM_SE16 numeric(16,4) DEFAULT 0,                            
   IM_SE17 numeric(16,4) DEFAULT 0,                            
   IM_SE18 numeric(16,4) DEFAULT 0,                            
   IM_SE19 numeric(16,4) DEFAULT 0,                            
   IM_SE20 numeric(16,4) DEFAULT 0,                                                                 
   CO_BANC varchar(3) NULL ,                            
   NU_CNTA_BANC varchar(20) NULL ,                            
   DE_CNTA_BANC varchar(100) NULL ,                            
   IM_SALD_INIC numeric(16,4) DEFAULT 0,                          
   TI_DETA_EMPR  varchar(1) NULL ,                          
   TI_DETA_BANC  varchar(1) NULL )                            
                        
                            
DECLARE                            
                            
@VNNU_MESE  TD_IN_001,                            
@VNNU_ANIO  TD_IN_001,                            
@VSNU_MESE  TD_VC_002,                            
@VSNU_ANIO  VARCHAR(4),                            
@VNNU_CONT_MESE TD_IN_001,                            
@VSDE_SELE VARCHAR(6000),                            
@VSCO_EMPR VARCHAR (50),                            
@VSDE_CAMP VARCHAR(7) ,                        
@VSCO_MONE_NACI TD_VC_003,                        
@VSCO_MONE_EXTR TD_VC_003,                      
@VNNU_FACT  NUMERIC(16,4),              
@VNFA_CAMB_MENS  NUMERIC(15,6),    
@VDFE_OPER_INIC TD_DT_001,    
@VDFE_OPER_FINA TD_DT_001                               
                            
    
    
SELECT @VSNU_ANIO = YEAR (@IDFE_OPER_QUIE)    
SELECT @VDFE_OPER_INIC = CONVERT (DATETIME, @VSNU_ANIO, 103)    
SELECT @VDFE_OPER_FINA = CONVERT (DATETIME, '31/12/' + @VSNU_ANIO, 103)    
    
   
EXECUTE SP_TCMOVI_BANC_EJEC_Q15 @ISCO_EMPR, @ISCA_WHER_0001, @ISCA_WHER_0002, @ISTI_PRES, @ISCO_MONE, @VDFE_OPER_INIC,      
@IDFE_OPER_QUIE    
                     
EXECUTE SP_TCMOVI_BANC_PROY_Q15 @ISCO_EMPR, @ISCA_WHER_0001, @ISCA_WHER_0002, @ISTI_PRES, @ISCO_MONE, @IDFE_OPER_QUIE,       
@VDFE_OPER_FINA      
      
                      
         
IF @ISTI_PRES = 'M'                        
 BEGIN      
      
 SELECT T1.CO_EMPR, MAX(T1.DE_EMPR), CONVERT(INT, SUM(T1.IM_SALD_FLUJ)), T1.CO_ARUB, MAX(T1.DE_ARUB), T1.CO_RUBR,      
MAX(T1.DE_RUBR), T1.CO_SUBR, MAX(T1.DE_SUBR),                           
 CONVERT (INT, SUM(ISNULL(T1.IM_SE01,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE02,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE03      
,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE04 ,0))),      
 CONVERT (INT, SUM(ISNULL(T1.IM_SE05,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE06,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE07      
,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE08 ,0))),       
 CONVERT (INT, SUM(ISNULL(T1.IM_SE09,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE10,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE11      
,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE12 ,0))),      
 CONVERT (INT, SUM(ISNULL(T1.IM_SE13,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE14,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE15      
,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE16 ,0))),       
 CONVERT (INT, SUM(ISNULL(T1.IM_SE17,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE18,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE19      
,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE20 ,0))), 


 CONVERT (INT, SUM(ISNULL(T1.IM_SE01,0)))+ CONVERT (INT, SUM(ISNULL(T1.IM_SE02,0)))+ CONVERT (INT, SUM(ISNULL(T1.IM_SE03      
,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE04 ,0))) +      
 CONVERT (INT, SUM(ISNULL(T1.IM_SE05,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE06,0)))+ CONVERT (INT, SUM(ISNULL(T1.IM_SE07      
,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE08 ,0)))+       
 CONVERT (INT, SUM(ISNULL(T1.IM_SE09,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE10,0)))+ CONVERT (INT, SUM(ISNULL(T1.IM_SE11      
,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE12 ,0)))+      
 CONVERT (INT, SUM(ISNULL(T1.IM_SE13,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE14,0)))+ CONVERT (INT, SUM(ISNULL(T1.IM_SE15      
,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE16 ,0)))+       
 CONVERT (INT, SUM(ISNULL(T1.IM_SE17,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE18,0)))+ CONVERT (INT, SUM(ISNULL(T1.IM_SE19      
,0))), CONVERT (INT, SUM(ISNULL(T1.IM_SE20 ,0))),


     
 T1.CO_BANC, T1.NU_CNTA_BANC, MAX(T1.DE_CNTA_BANC), MAX(T1.TI_DETA_EMPR), MAX(T1.TI_DETA_BANC)                          
 FROM #TWFLUJ_CAJA_Q15_1 T1                           
 GROUP BY T1.CO_EMPR, T1.CO_ARUB, T1.CO_RUBR, T1.CO_SUBR, T1.CO_BANC, T1.NU_CNTA_BANC                
      
 END      
ELSE   

   
 BEGIN                        
 SELECT T1.CO_EMPR, MAX(T1.DE_EMPR), SUM(T1.IM_SALD_FLUJ), T1.CO_ARUB, MAX(T1.DE_ARUB), T1.CO_RUBR, MAX(T1.DE_RUBR),      
T1.CO_SUBR, MAX(T1.DE_SUBR),                           
 SUM(ISNULL(T1.IM_SE01,0)), SUM(ISNULL(T1.IM_SE02,0)), SUM(ISNULL(T1.IM_SE03 ,0)), SUM(ISNULL(T1.IM_SE04 ,0)),      
 SUM(ISNULL(T1.IM_SE05,0)), SUM(ISNULL(T1.IM_SE06,0)), SUM(ISNULL(T1.IM_SE07 ,0)), SUM(ISNULL(T1.IM_SE08 ,0)),      
 SUM(ISNULL(T1.IM_SE09,0)), SUM(ISNULL(T1.IM_SE10,0)), SUM(ISNULL(T1.IM_SE11 ,0)), SUM(ISNULL(T1.IM_SE12 ,0)),       
 SUM(ISNULL(T1.IM_SE13,0)), SUM(ISNULL(T1.IM_SE14,0)), SUM(ISNULL(T1.IM_SE15 ,0)), SUM(ISNULL(T1.IM_SE16 ,0)),      
 SUM(ISNULL(T1.IM_SE17,0)), SUM(ISNULL(T1.IM_SE18,0)), SUM(ISNULL(T1.IM_SE19 ,0)), SUM(ISNULL(T1.IM_SE20 ,0)), 
 
 SUM(ISNULL(T1.IM_SE01,0))+ SUM(ISNULL(T1.IM_SE02,0))+ SUM(ISNULL(T1.IM_SE03 ,0))+ SUM(ISNULL(T1.IM_SE04 ,0))+      
 SUM(ISNULL(T1.IM_SE05,0))+ SUM(ISNULL(T1.IM_SE06,0))+ SUM(ISNULL(T1.IM_SE07 ,0))+ SUM(ISNULL(T1.IM_SE08 ,0))+      
 SUM(ISNULL(T1.IM_SE09,0))+ SUM(ISNULL(T1.IM_SE10,0))+ SUM(ISNULL(T1.IM_SE11 ,0))+ SUM(ISNULL(T1.IM_SE12 ,0))+       
 SUM(ISNULL(T1.IM_SE13,0))+ SUM(ISNULL(T1.IM_SE14,0))+ SUM(ISNULL(T1.IM_SE15 ,0))+ SUM(ISNULL(T1.IM_SE16 ,0))+      
 SUM(ISNULL(T1.IM_SE17,0))+ SUM(ISNULL(T1.IM_SE18,0))+ SUM(ISNULL(T1.IM_SE19 ,0))+ SUM(ISNULL(T1.IM_SE20 ,0)),
 
                  
 T1.CO_BANC, T1.NU_CNTA_BANC, MAX(T1.DE_CNTA_BANC), MAX(T1.TI_DETA_EMPR), MAX(T1.TI_DETA_BANC)                          
 FROM #TWFLUJ_CAJA_Q15_1 T1                                
 GROUP BY T1.CO_EMPR, T1.CO_ARUB, T1.CO_RUBR, T1.CO_SUBR, T1.CO_BANC, T1.NU_CNTA_BANC                
 END      
      
                            
RETURN                      
                            
/********************************* FIN ***********************************/ 

GO
